USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[ShuppatsuKetsujoList]    Script Date: 07/20/2016 00:00:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShuppatsuKetsujoList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ShuppatsuKetsujoList]
GO

CREATE PROCEDURE [dbo].[ShuppatsuKetsujoList]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
	@ROUNDNO int,
	@KUMINO int,
	@KYOGISYAFLG int
AS
	SELECT
			*
		FROM (
			SELECT DISTINCT
					(CONVERT(varchar, LA.TAIKAINO) + '-' + 
					CONVERT(varchar, LA.KYOGINO) + '-' + 
					CONVERT(varchar, LA.SUBNO) + '-' + 
					CONVERT(varchar, LA.ROUNDNO) + '-' + 
					CONVERT(varchar, LA.KUMINO) + '-' + 
					CONVERT(varchar, LA.LANE)
					) AS KEKKAFLG,
					LA.LANE, 
					(KS.NUMBER1 + CONVERT(varchar, KS.NUMBER2)) AS NUMBER, 
					(CONVERT(varchar, KS.KYOGISYAKANA) + '<br/>' + CONVERT(varchar, KS.KYOGISYAMEI)) AS KYOGISYAMEI, 
					(CONVERT(varchar, S.SYOZOKUMEI) + '<br/>' + CONVERT(varchar, KS.KOJINSYOZOKUTIMEI)) AS SYOZOKUMEI,
					CASE LA.SYUKKETU WHEN 2 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS SYUKKETU
				FROM TBL_LANE AS LA,
					TBL_KYOGISYA AS KS, 
					TBL_SYOZOKU AS S
				WHERE LA.TAIKAINO = @TAIKAINO 
					AND LA.KYOGINO = @KYOGINO 
					AND LA.SUBNO = @SUBNO 
					AND LA.ROUNDNO = @ROUNDNO 
					AND LA.KUMINO = @KUMINO 
					AND LA.TAIKAINO = KS.TAIKAINO 
					AND LA.KYOGISYANO = KS.KYOGISYANO 
					AND KS.TAIKAINO = S.TAIKAINO 
					AND KS.SYOZOKUNO1 = S.SYOZOKUNO 
					AND KS.KYOGISYAFLG = @KYOGISYAFLG
		) X
		ORDER BY 
			X.LANE ASC
GO

